﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace socialnexus.classes.models
{
    public class SiteUser
    {
        public static string TableName = "SiteUsers";

        private int _SiteUserId;
        public int SiteUserId
        {
            get { return _SiteUserId; }
            set { _SiteUserId = value; }
        }

        private string _FirstName;
        public string FirstName
        {
            get { return _FirstName; }
            set { _FirstName = value; }
        }

        private string _LastName;
        public string LastName
        {
            get { return _LastName; }
            set { _LastName = value; }
        }

        private string _PictureURL;
        public string PictureURL
        {
            get { return _PictureURL; }
            set { _PictureURL = value; }
        }

        private bool _IsActive;
        public bool IsActive
        {
            get { return _IsActive; }
            set { _IsActive = value; }
        }

        private string _Username;
        public string Username
        {
            get { return _Username; }
            set { _Username = value; }
        }


        private string _Password;
        public string Password
        {
            get { return _Password; }
            set { _Password = value; }
        }

        public static SiteUser Authenticate(string username, string password)
        {
            SiteUser su = null;

            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["socialnexusConnectionString"].ConnectionString);
            con.Open();

            SqlCommand cmd = new SqlCommand("SELECT TOP 1 *, Username FROM " +
                TableName + " WHERE Username = @USERNAME " +
                " AND Password = @PASSWORD", con);
            cmd.Parameters.Add(new SqlParameter("@USERNAME", username));
            cmd.Parameters.Add(new SqlParameter("@PASSWORD", password));

            SqlDataReader reader = cmd.ExecuteReader();

            if (reader.Read())
            {
                su = new SiteUser();
                su.SiteUserId = (int)reader["SiteUserId"];
                su.FirstName = reader["FirstName"].ToString().Trim();
                su.LastName = reader["LastName"].ToString().Trim();
                su.PictureURL = reader["PictureURL"].ToString().Trim();
                su.IsActive = reader["IsActive"].ToString() == "True";
                su.Username = reader["Username"].ToString();
            }

            con.Close();


            return su;
        }

        public static DataTable GetAll() {
            DataTable users = new DataTable(TableName);

            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["socialnexusConnectionString"].ConnectionString);
            con.Open();

            SqlCommand cmd = new SqlCommand("SELECT * FROM " + TableName, con);
            SqlDataReader reader = cmd.ExecuteReader();
            users.Load(reader);

            con.Close();

            return users;
        }

        public static SiteUser GetById(string id) {
            SiteUser su = null;

            if (String.IsNullOrEmpty(id))
                return su;

            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["socialnexusConnectionString"].ConnectionString);
            con.Open();

            SqlCommand cmd = new SqlCommand("SELECT * FROM " + 
                TableName + " WHERE SiteUserId = @ID", con);
            cmd.Parameters.Add(new SqlParameter("@ID", id));

            SqlDataReader reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                su = new SiteUser();
                su.SiteUserId = (int)reader["SiteUserId"];
                su.FirstName = reader["FirstName"].ToString().Trim();
                su.LastName = reader["LastName"].ToString().Trim();
                su.PictureURL = reader["PictureURL"].ToString().Trim();
                su.IsActive = reader["IsActive"].ToString() == "True";
                su.Username = reader["Username"].ToString().Trim();
            }

            con.Close();

            return su;
        }

        public static bool UserNameExists(string username)
        {
            bool result = false;

            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["socialnexusConnectionString"].ConnectionString);
            con.Open();
            SqlCommand cmd = new SqlCommand("SELECT * FROM " + TableName +
                " WHERE Username = @USERNAME AND IsActive = 1", con);
            cmd.Parameters.Add(new SqlParameter("@USERNAME", username));

            SqlDataReader reader = cmd.ExecuteReader();
            result = reader.HasRows;

            con.Close();

            return result;
        }

        public static DataTable Search(string keyword)
        {
            return Search(keyword, null);
        }
        
        public static DataTable Search(string keyword, string conditions)
        {
            DataTable users = new DataTable("Users");

            keyword = keyword.Trim();

            string[] words = keyword.Split(' ');
            string[] fields = {"FirstName", "LastName"};
            
            List<string> queryParts = new List<string>();
            foreach (string field in fields)
                foreach(string word in words)
                    queryParts.Add(field + " LIKE '%" + word.Trim().Replace("'", "''") + "%'");

            string query = String.Join(" OR ", queryParts.ToArray());

            List<String> where = new List<string>();
            if(!String.IsNullOrEmpty(query))
                where.Add("(" + query + ")");

            if(!String.IsNullOrEmpty(conditions))
                where.Add("(" + conditions + ")");

            string whereSQL = string.Join(" AND ", where.ToArray());

            string sql = "SELECT * FROM " + TableName;
            if (!String.IsNullOrEmpty(whereSQL))
                sql += " WHERE " + whereSQL;
            
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["socialnexusConnectionString"].ConnectionString);
            con.Open();

            SqlCommand cmd = new SqlCommand(sql, con);

            SqlDataReader reader = cmd.ExecuteReader();
            users.Load(reader);

            con.Close();

            return users;
        }

        public void Update()
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["socialnexusConnectionString"].ConnectionString);
            con.Open();

            SqlCommand cmd = new SqlCommand("UPDATE " + TableName +
                " SET FirstName = @FIRSTNAME, LastName = @LASTNAME, " +
                "PictureURL = @PICTUREURL, IsActive = @ISACTIVE" +
                " WHERE SiteUserId = @ID", con);
            cmd.Parameters.Add(new SqlParameter("@ID", SiteUserId));
            cmd.Parameters.Add(new SqlParameter("@FIRSTNAME", FirstName));
            cmd.Parameters.Add(new SqlParameter("@LASTNAME", LastName));
            cmd.Parameters.Add(new SqlParameter("@PICTUREURL", PictureURL));
            cmd.Parameters.Add(new SqlParameter("@ISACTIVE", IsActive));

            cmd.ExecuteNonQuery();

            con.Close();
        }

        public void Save()
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["socialnexusConnectionString"].ConnectionString);
            con.Open();

            SqlCommand cmd = new SqlCommand("INSERT INTO " + TableName +
                " (Username, Password, FirstName, LastName, PictureURL, IsActive) VALUES " +
                " (@USERNAME, @PASSWORD, @FIRSTNAME, @LASTNAME, @PICTUREURL, @ISACTIVE)", con);
            cmd.Parameters.Add(new SqlParameter("@USERNAME", Username));
            cmd.Parameters.Add(new SqlParameter("@PASSWORD", Password));
            cmd.Parameters.Add(new SqlParameter("@FIRSTNAME", FirstName));
            cmd.Parameters.Add(new SqlParameter("@LASTNAME", LastName));
            cmd.Parameters.Add(new SqlParameter("@PICTUREURL", PictureURL));
            cmd.Parameters.Add(new SqlParameter("@ISACTIVE", IsActive));

            cmd.ExecuteNonQuery();

            con.Close();
        }

        public void AddFriend(string FriendId)
        {
            // First check that the user with Id = FriendId exists
            if (!String.IsNullOrEmpty(FriendId) && SiteUserId.ToString() != FriendId &&  
                SiteUser.GetById(FriendId) != null)
            {
                SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["socialnexusConnectionString"].ConnectionString);
                con.Open();
                SqlCommand cmd = new SqlCommand("INSERT INTO " + Friend.TableName +
                    " (User1Id, User2Id) VALUES (@U1, @U2), (@U2, @U1)", con);
                cmd.Parameters.Add(new SqlParameter("@U1", SiteUserId));
                cmd.Parameters.Add(new SqlParameter("@U2", FriendId));

                cmd.ExecuteNonQuery();

                con.Close();
            }
        }

        public void RemoveFriend(string FriendId)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["socialnexusConnectionString"].ConnectionString);
            con.Open();
            SqlCommand cmd = new SqlCommand("DELETE FROM " + Friend.TableName +
                " WHERE (User1Id = @U1 AND User2Id = @U2) OR (User1Id = @U2 AND User2Id = @U1)", con);
            cmd.Parameters.Add(new SqlParameter("@U1", SiteUserId));
            cmd.Parameters.Add(new SqlParameter("@U2", FriendId));

            cmd.ExecuteNonQuery();

            con.Close();
        }

        public DataTable GetFriends()
        {
            DataTable friends = new DataTable("Friends");

            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["socialnexusConnectionString"].ConnectionString);
            con.Open();
            SqlCommand cmd = new SqlCommand("SELECT * FROM " + Friend.TableName + " INNER JOIN " +
                TableName + " ON " + TableName + ".SiteUserId = " + Friend.TableName + 
                ".User2Id WHERE User1Id = @ID AND IsActive = 1", con);
            cmd.Parameters.Add(new SqlParameter("@ID", SiteUserId));

            SqlDataReader reader = cmd.ExecuteReader();

            friends.Load(reader);

            con.Close();

            return friends;
        }

        public List<string> GetFriendIds()
        {
            List<string> friendIds = new List<string>();

            DataTable friends = GetFriends();
            foreach (DataRow dr in friends.Rows)
            {
                friendIds.Add(dr["SiteUserId"].ToString());
            }

            return friendIds;
        }


        public void SendMessage(string FriendId, string VideoID)
        {
            if (GetFriendIds().Contains(FriendId))
            {
                SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["socialnexusConnectionString"].ConnectionString);
                con.Open();
                SqlCommand cmd = new SqlCommand("INSERT INTO Messages (FromUserId, ToUserId, VideoURL) " + 
                    " VALUES (@FROMUSERID, @TOUSERID, @VIDEOURL) ", con);
                cmd.Parameters.Add(new SqlParameter("@FROMUSERID", SiteUserId));
                cmd.Parameters.Add(new SqlParameter("@TOUSERID", FriendId));
                string videoURL = ConfigurationManager.AppSettings["Red5URL"].ToString() + "streams/" +
                    VideoID + ".flv";
                cmd.Parameters.Add(new SqlParameter("@VIDEOURL", videoURL));

                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }
}
